package com.ucode.wap.hrm.service;

import cn.hutool.core.util.IdcardUtil;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import com.ucode.hrm.service.*;
import com.ucode.model.hrm.*;
import com.ucode.model.hrm.convert.HrmEmployeeContractConvert;
import com.ucode.model.hrm.enums.HrmCooperationStatusEnum;
import com.ucode.model.hrm.enums.HrmCustomerProjectStatusEnum;
import com.ucode.model.hrm.enums.HrmEmployeeContractStatusEnum;
import com.ucode.model.hrm.enums.HrmEmploymentTypeEnum;
import com.ucode.tool.base.enums.SexEnum;
import com.ucode.tool.util.IdentifyUtils;
import com.ucode.tool.util.JsonUtils;
import com.ucode.wap.hrm.dto.HrmExcelEmployee;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;

/**
 * Excel 处理 服务
 * @Author: liliang
 * @Date: 2025/6/29 19:49
 * @Version: 1.0
 */
@Service
public class ApiHrmExcelHandleService {

    private static Log log = LogFactory.get();

    @Resource
    private HrmCustomerCompanyService hrmCustomerCompanyService;
    @Resource
    private HrmCustomerProjectService hrmCustomerProjectService;
    @Resource
    private HrmEmployeeService hrmEmployeeService;
    @Resource
    private HrmEmployeeCompensationService hrmEmployeeCompensationService;
    @Resource
    private HrmContractTplService hrmContractTplService;
    @Resource
    private HrmCompanyService companyService;
    @Resource
    private HrmEmployeeContractService employeeContractService;
    /**
     * 导入员工数据
     * @param file
     */
    public void importEmployeesData(MultipartFile file) throws Exception {
        List<HrmExcelEmployee> employees = parseExcelEmployees(file);

        Map<String, HrmCustomerCompany> customerCompanyMap = new HashMap<>();
        Map<String, HrmCustomerProject> customerProjectMap = new HashMap<>();

        HrmCompany company = companyService.validate(1940381662922752000L);
        int index = 1;
        for(HrmExcelEmployee employee : employees){

            log.info("开始处理员工数据："+ JsonUtils.toJsonString( employee)+",当前第"+index+"条");

            if(StrUtil.isBlank(employee.getSuperiorUnit()) || StrUtil.isBlank(employee.getEmploymentUnit())){
                log.info("数据不完整："+ JsonUtils.toJsonString( employee));
                continue;
            }

            //保存客户
            HrmCustomerCompany customerCompany = customerCompanyMap.get(employee.getSuperiorUnit().trim());
            if(ObjUtil.isNull(customerCompany)){
                customerCompany = hrmCustomerCompanyService.queryOneByName(employee.getSuperiorUnit().trim());
                if(ObjUtil.isNull(customerCompany)) {
                    customerCompany = new HrmCustomerCompany();
                    customerCompany.setCompanyName(employee.getSuperiorUnit().trim());
                    customerCompany.setStatus(HrmCooperationStatusEnum.IN_PROGRESS.getCode());
                    customerCompany.setId(hrmCustomerCompanyService.insert(customerCompany));
                }

                customerCompanyMap.put(customerCompany.getCompanyName(),customerCompany);
            }

            //保存项目
            HrmCustomerProject customerProject = customerProjectMap.get(customerCompany.getId()+employee.getEmploymentUnit().trim());
            if(ObjUtil.isNull(customerProject)){
                customerProject = hrmCustomerProjectService.queryOneByName(customerCompany.getId(),employee.getEmploymentUnit().trim());
                if(ObjUtil.isNull(customerProject)) {
                    customerProject = new HrmCustomerProject();
                    customerProject.setCustomerCompanyId(customerCompany.getId());
                    customerProject.setProjectName(employee.getEmploymentUnit().trim());
                    customerProject.setStatus(HrmCustomerProjectStatusEnum.ONGOING.getCode());
                    customerProject.setOwnerAdminId(1L);
                    customerProject.setId(hrmCustomerProjectService.insert(customerProject));
                }

                customerCompanyMap.put(customerCompany.getCompanyName(),customerCompany);
            }

            //保存员工
            HrmEmployee hrmEmployee = new HrmEmployee();
            hrmEmployee.setId(IdentifyUtils.getDefaultSnowflakeId());
            hrmEmployee.setRealName(employee.getName());

            hrmEmployee.setGender(IdcardUtil.getGenderByIdCard(employee.getIdNumber()) == 1 ? SexEnum.MALE.getCode() : SexEnum.FEMALE.getCode());
            hrmEmployee.setEthnicity(employee.getEthnicity());
            //if(ObjUtil.isNotNull(employee.getBirthDate())){
            //    hrmEmployee.setBirthDate(employee.getBirthDate().toInstant()
            //            .atZone(ZoneId.systemDefault())
            //            .toLocalDate());
            //}

            Map<String,Integer> EducationMap = new HashMap<>();
            EducationMap.put("初中及以下",1);
            EducationMap.put("高中/中专",2);
            EducationMap.put("大专",3);
            EducationMap.put("本科",4);
            EducationMap.put("硕士",5);
            EducationMap.put("博士",6);
            EducationMap.put("其他",10);
            if(StrUtil.isNotBlank(employee.getEducation())){
                hrmEmployee.setEducation(EducationMap.get(employee.getEducation()));
            }

            Map<String,Integer> maritalStatusMap = new HashMap<>();
            maritalStatusMap.put("未婚",1);
            maritalStatusMap.put("已婚",2);
            maritalStatusMap.put("离异",3);
            maritalStatusMap.put("丧偶",4);
            if(StrUtil.isNotBlank(employee.getMaritalStatus())){
                hrmEmployee.setMaritalStatus(maritalStatusMap.get(employee.getMaritalStatus()));
            }

            hrmEmployee.setHouseholdType(employee.getHouseholdType());

            //根据身份证号码获取籍贯
            hrmEmployee.setRegisteredResidence(IdcardUtil.getProvinceByIdCard(employee.getIdNumber()));
            hrmEmployee.setDetailedAddress(employee.getRegisteredAddress());

            hrmEmployee.setCnontactPhone(employee.getEmergencyContact());
            hrmEmployee.setIdCardNumber(employee.getIdNumber());
            //从身份证中获取出生日期
            hrmEmployee.setBirthDate(IdcardUtil.getBirthDate(employee.getIdNumber()).toLocalDateTime().toLocalDate());


            hrmEmployee.setPhone(employee.getPersonalPhone());
            hrmEmployee.setEmail(employee.getEmail());
            if(ObjUtil.isNotNull(employee.getEntryDate())){
                hrmEmployee.setHireDate(employee.getEntryDate().toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            if(ObjUtil.isNotNull(employee.getProbationEndDate())){
                hrmEmployee.setTrialEndDate(employee.getProbationEndDate().toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            if(ObjUtil.isNotNull(employee.getContractEndDate())){
                hrmEmployee.setContractEndDate(employee.getContractEndDate().toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            //离职日期与离职状态
            if(ObjUtil.isNotNull(employee.getResignationDate())){
                hrmEmployee.setResignationDate(cn.hutool.core.date.DateUtil.toLocalDateTime(employee.getResignationDate()).toLocalDate());
            }

            if(ObjUtil.isNotNull(employee.getResignationDate())){
                hrmEmployee.setEmploymentType(HrmEmploymentTypeEnum.LEFT.getCode());
            }else if(ObjUtil.isNotNull(hrmEmployee.getTrialEndDate()) && hrmEmployee.getTrialEndDate().isAfter(LocalDate.now())){
                hrmEmployee.setEmploymentType(HrmEmploymentTypeEnum.PROBATION.getCode());
            }else {
                hrmEmployee.setEmploymentType(HrmEmploymentTypeEnum.FULL_TIME.getCode());
            }
            //hrmEmployee.setEmploymentType(ObjUtil.isNotNull(employee.getResignationDate()) ? HrmEmploymentTypeEnum.LEFT.getCode() : HrmEmploymentTypeEnum.FULL_TIME.getCode());

            hrmEmployee.setCompanyId(customerCompany.getId());
            hrmEmployee.setProjectId(customerProject.getId());
            hrmEmployee.setCustomCompany(employee.getEmploymentUnit());
            hrmEmployee.setDepartmentName(employee.getRecruitmentStatus());//这里excel没有部门名称，使用上级部门名称代替
            hrmEmployee.setPositionName(employee.getJobTitle());
            hrmEmployee.setWorkLocation(employee.getArea());
            if(ObjUtil.isNotNull(employee.getRenewalCount()) || employee.getRenewalCount() > 0){
                hrmEmployee.setContractType(2);
            }else {
                hrmEmployee.setContractType(1);
            }

            //合同终止
            if (hrmEmployee.getEmploymentType() == HrmEmploymentTypeEnum.LEFT.getCode()) {
                hrmEmployee.setContractStatus(HrmEmployeeContractStatusEnum.TERMINATED.getCode());
            }else if(ObjUtil.isNotNull(employee.getContractEndDate()) && employee.getContractEndDate().before(new Date())){
                //合同到期
                hrmEmployee.setContractStatus(HrmEmployeeContractStatusEnum.EXPIRED.getCode());
            }else {
                hrmEmployee.setContractStatus(HrmEmployeeContractStatusEnum.COMPLETED_SIGNED.getCode());
            }

            try {
                hrmEmployeeService.insert(hrmEmployee);
            }catch (Exception e) {
                //这里添加失败的，基本上身份证号重复了
                continue;
            }

            //添加员工薪酬
            HrmEmployeeCompensation hrmEmployeeCompensation = new HrmEmployeeCompensation();
            hrmEmployeeCompensation.setId(hrmEmployee.getId());
            hrmEmployeeCompensation.setBankName(employee.getBank());
            hrmEmployeeCompensation.setBankCard(employee.getBankAccount());
            hrmEmployeeCompensation.setBaseSalary(new BigDecimal(employee.getBasicSalary()));
            hrmEmployeeCompensation.setSocialSecurityDate(hrmEmployee.getHireDate());
            hrmEmployeeCompensation.setSocialSecurityRuleId(1937900357991596032L);//默认社保规则
            hrmEmployeeCompensation.setHousingFundBase(BigDecimal.ZERO);
            hrmEmployeeCompensation.setHousingFundEnterpriseRate(BigDecimal.ZERO);
            hrmEmployeeCompensation.setHousingFundPersonalRate(BigDecimal.ZERO);

            hrmEmployeeCompensationService.insert(hrmEmployeeCompensation);

            //未离职,且合同已到期
            if(ObjUtil.equals(HrmEmploymentTypeEnum.FULL_TIME.getCode(),hrmEmployee.getEmploymentType()) &&
                    ObjUtil.equals(HrmEmployeeContractStatusEnum.EXPIRED.getCode(),hrmEmployee.getContractStatus()) &&
                ObjUtil.isNotNull(employee.getContractXqEndDate())){
                Long contractTplId = 0L;
                if("否".equals(employee.getSocialSecurityLocation())) {
                    //无社保合同模板
                    contractTplId = 1938246218395222024L;
                }else {
                    contractTplId = 1938246218395222025L;
                }

                HrmContractTpl contractTpl = hrmContractTplService.selectByPrimaryKey(contractTplId);
                HrmEmployeeContract employeeContract = HrmEmployeeContractConvert.INSTANCE.build(hrmEmployee,hrmEmployeeCompensation,company,contractTpl);
                employeeContract.setContractType(2);
                employeeContract.setStartDate(hrmEmployee.getContractEndDate());
                employeeContract.setEndDate(cn.hutool.core.date.DateUtil.toLocalDateTime(employee.getContractXqEndDate()).toLocalDate());
                employeeContract.setTrialEndDate(null);

                employeeContractService.insert(employeeContract);
            }

            index++;
        }

    }

    /**
     * 解析Excel文件并返回员工列表
     * @param file
     * @return
     * @throws IOException
     */
    public List<HrmExcelEmployee> parseExcelEmployees(MultipartFile file) throws IOException {
        List<HrmExcelEmployee> employees = new ArrayList<>(); // 创建空列表存储结果
        Workbook workbook = WorkbookFactory.create(file.getInputStream()); // 从输入流创建工作簿

        //第一个工作表：在职员工  第二个工作表：离职员工
        for (int sheetIndex = 0; sheetIndex <= 1; sheetIndex++) {

            Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取工作表
            // 遍历工作表的每一行（从第1行开始，跳过标题行）

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i); // 获取当前行
                if (row == null) continue; // 跳过空行

                HrmExcelEmployee employee = new HrmExcelEmployee(); // 创建新员工对象

                // 解析每个单元格并设置到员工对象中
                // 索引对应Excel列：0=A列, 1=B列, 2=C列...
                employee.setSerialNumber(getIntValue(row.getCell(0))); // A列: 序号
                employee.setName(getStringValue(row.getCell(1))); // B列: 姓名
                employee.setNature(getStringValue(row.getCell(2))); // C列: 性质
                employee.setSuperiorUnit(getStringValue(row.getCell(3))); // D列: 上级单位
                employee.setEmploymentUnit(getStringValue(row.getCell(4))); // E列: 用工单位
                employee.setRecruitmentStatus(getStringValue(row.getCell(5))); // F列: 招聘情况
                employee.setArea(getStringValue(row.getCell(6))); // G列: 地区
                employee.setPosition(getStringValue(row.getCell(7))); // H列: 岗位
                employee.setBasicSalary(getDoubleValue(row.getCell(8))); // I列: 基本工资
                employee.setGender(getStringValue(row.getCell(9))); // J列: 性别
                employee.setEmploymentStatus(getStringValue(row.getCell(10))); // K列: 在职状态
                employee.setEntryDate(getDateValue(row.getCell(11))); // L列: 入职日期
                employee.setContractSignMethod(getStringValue(row.getCell(12))); // M列: 合同签署小程序/线下
                employee.setContractExpiryReminder(getStringValue(row.getCell(13))); // N列: 合同到期提醒
                employee.setRenewalCount(getIntValue(row.getCell(14))); // O列: 续签次数
                employee.setResignationDate(getDateValue(row.getCell(15))); // P列: 离职日期
                employee.setEmploymentDays(getIntValue(row.getCell(16))); // Q列: 在职天数
                employee.setContractSigned(getStringValue(row.getCell(17))); // R列: 合同
                employee.setSocialSecurityLocation(getStringValue(row.getCell(18))); // S列: 社保公积金缴纳地
                employee.setJobTitle(getStringValue(row.getCell(19))); // T列: 职位
                employee.setIdNumber(getStringValue(row.getCell(20))); // U列: 身份证号
                employee.setBirthDate(getDateValue(row.getCell(21))); // V列: 出生日期
                employee.setEducation(getStringValue(row.getCell(22))); // W列: 学历
                employee.setEthnicity(getStringValue(row.getCell(23))); // X列: 民族
                employee.setMaritalStatus(getStringValue(row.getCell(24))); // Y列: 婚姻状况
                employee.setHouseholdType(getStringValue(row.getCell(25))); // Z列: 户口性质
                employee.setRegisteredAddress(getStringValue(row.getCell(26))); // AA列: 户籍所在地地址
                employee.setCurrentAddress(getStringValue(row.getCell(27))); // AB列: 现居住地地址
                employee.setEmail(getStringValue(row.getCell(28))); // AC列: 邮箱
                employee.setPersonalPhone(getStringValue(row.getCell(29))); // AD列: 本人联系方法
                employee.setEmergencyContact(getStringValue(row.getCell(30))); // AE列: 第一紧急联系人电话
                employee.setProbationPeriod(getIntValue(row.getCell(31))); // AF列: 试用期
                employee.setProbationEndDate(getDateValue(row.getCell(32))); // AG列: 试用期到期日
                employee.setContractTerm(getIntValue(row.getCell(33))); // AH列: 合同期
                employee.setContractEndDate(getDateValue(row.getCell(34))); // AI列: 合同到期日
                employee.setAge(getIntValue(row.getCell(35))); // AJ列: 年龄
                employee.setIdCopy(getStringValue(row.getCell(36))); // AK列: 身份证复印件
                employee.setMedicalReport(getStringValue(row.getCell(37))); // AL列: 体检报告
                employee.setEducationCertificate(getStringValue(row.getCell(38))); // AM列: 学历证明
                employee.setResignationCertificate(getStringValue(row.getCell(39))); // AN列: 离职证明
                employee.setPhoto(getStringValue(row.getCell(40))); // AO列: 相片
                employee.setBank(getStringValue(row.getCell(41))); // AP列: 银行
                employee.setBankAccount(getStringValue(row.getCell(42))); // AQ列: 银行卡号
                employee.setOtherBankAccount(getStringValue(row.getCell(43))); // AR列: 其他银行卡号
                employee.setEmployerLiabilityInsurance(getStringValue(row.getCell(44)));// AS列: 雇主责任险
                employee.setContractXqEndDate(getDateValue(row.getCell(45)));// AS列: 合同到期续期截止日期
                // 可根据需要继续解析剩余列（AT到AU列）...

                employees.add(employee); // 将员工对象添加到结果列表
            }

        }

        workbook.close(); // 关闭工作簿释放资源

        log.info("解析完成，共有员工数据：{}条", employees.size());
        return employees;
    }


    // 获取单元格整数值
    private Integer getIntValue(Cell cell) {
        if (cell == null) return null; // 空单元格返回null

        try {
            // 尝试获取数字值并转为整数
            return (int) cell.getNumericCellValue();
        } catch (Exception e) {
            // 转换失败时尝试从字符串解析
            try {
                return Integer.parseInt(getStringValue(cell));
            } catch (NumberFormatException ex) {
                return null; // 解析失败返回null
            }
        }
    }

    // 获取单元格浮点数值
    private Double getDoubleValue(Cell cell) {
        if (cell == null) return null; // 空单元格返回null

        try {
            // 尝试获取数字值
            return cell.getNumericCellValue();
        } catch (Exception e) {
            // 转换失败时尝试从字符串解析
            try {
                return Double.parseDouble(getStringValue(cell));
            } catch (NumberFormatException ex) {
                return null; // 解析失败返回null
            }
        }
    }

    // 获取单元格日期值
    private Date getDateValue(Cell cell) {
        if (cell == null) return null; // 空单元格返回null

        try {
            // 检查是否为日期格式
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue(); // 返回日期对象
            }
            // 尝试从字符串解析日期
            String strValue = getStringValue(cell);
            if (strValue != null && !strValue.isEmpty()) {
                return new SimpleDateFormat("yyyy-MM-dd").parse(strValue);
            }
            return null; // 无法解析返回null
        } catch (Exception e) {
            return null; // 异常时返回null
        }
    }


    // 获取单元格字符串值
    private String getStringValue(Cell cell) {
        if (cell == null) return null; // 空单元格返回null

        switch (cell.getCellType()) {
            case STRING: // 文本类型
                return cell.getStringCellValue().trim(); // 返回去除空格的文本
            case NUMERIC: // 数字类型
                if (DateUtil.isCellDateFormatted(cell)) { // 检查是否是日期格式
                    // 日期格式转为"yyyy-MM-dd"字符串
                    return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                }
                // 普通数字转为字符串（避免科学计数法）
                return String.valueOf((long) cell.getNumericCellValue());
            case BOOLEAN: // 布尔类型
                return String.valueOf(cell.getBooleanCellValue()); // 转为字符串
            case FORMULA: // 公式类型
                // 获取公式计算结果
                return cell.getCellFormula();
            default: // 其他类型
                return ""; // 返回空字符串
        }
    }

}
